const pool = require("../connection/sqlPool");

async function REGISTER(e_mail,username,password){
    const promisePool = pool.promise();
    try{
        const [rows_w,fileds_w] = await promisePool.query('INSERT INTO `User`(`e_mail`,`user_name`,`password`) VALUES (?,?,?)',[e_mail,username,password]);
        // const [rows_m,fileds_m] = await promisePool.query('INSERT INTO `user_message`(`username`) VALUES (?)',[username]);
        // return {wx_user:rows_w,user_message:rows_m}; 
        return {user:rows_w}; 
    }catch(e){
        return e.message
    }  
    
}

async function DEBARK(username,password){
    const promisePool = pool.promise();
    try{
        const [rows,fileds] = await promisePool.query('SELECT * FROM `User` WHERE `user_name`=? AND `password`=?',[username,password]);
        if(rows.length){
            return {msg:'登录成功',username:rows[0].username}
        }else{
            return {msg:rows}
        }
    }catch(error){
        return {msg:'未进行登录',Error:error.message}
    }
}

async function ADDLEVEL(username,adding){
    const promisePool = pool.promise();
    try{
        const [rows,fileds] = await promisePool.query('UPDATE `user_message` SET `level_num` = `level_num`+? WHERE `username`=?',[adding,username]);
        return rows
    }catch(error){
        return {tips:'更改失败',msg:error.message}
    }
}

async function RENAME(username,rename){
    const promisePool = pool.promise();
    try{
        const [rows_m,fileds_m] = await promisePool.query('UPDATE `user_message` SET `username` = ? WHERE `username`=?',[rename,username]);
        const [rows_r,fildes_r] = await promisePool.query('UPDATE `Word_recite` SET `username` = ? WHERE `username`=?',[rename,username]);
        const [rows_w,fileds_w] = await promisePool.query('UPDATE `Wx_user` SET `username` = ? WHERE `username`=?',[rename,username]);
        const [rows_hR,fildes_hR] = await promisePool.query('UPDATE `game_history` SET `usernameR` = ? WHERE `usernameR`=?',[rename,username]);
        const [rows_hB,fildes_hB] = await promisePool.query('UPDATE `game_history` SET `usernameB` = ? WHERE `usernameB`=?',[rename,username]);
        const [rows_fh,fileds_fh] = await promisePool.query('UPDATE `Friends` SET `host` = ? WHERE `host`=?',[rename,username]);
        const [rows_ff,fileds_ff] = await promisePool.query('UPDATE `Friends` SET `friend` = ? WHERE `friend`=?',[rename,username]);
        return {tips:'更改成功',msg:rename,rows_m,rows_r,rows_w,rows_hR,rows_hB,rows_fh,rows_ff};
    }catch(error){
        return {tips:'更改失败',msg:error.message}
    }
}

//邀请人发起请求，添加好友申请发送至申请表中
async function APPLICANT(host,aim){
    const promisePool = pool.promise();
    try{
        const [rows,fileds] = await promisePool.query('INSERT `friends_applicant`(`host`,`aim`)VALUES(?,?)',[host,aim]);
        return {tips:'好友申请发送成功',rows};
    }catch(error){
        return {tips:'好友申请发送失败',msg:error.message};
    }
}
//被邀请人查找邀请
async function RECEIVE(own){
    const promisePool = pool.promise();
    try{
        const [rows,fileds] = await promisePool.query('SELECT * FROM `friends_applicant` WHERE `aim` = ?',[own]);
        return rows;
    }catch(error){
        return {tips:'查找失败',msg:error.message};
    }
}
//由邀请人发起，发起后被邀请人同意，双向加入好友表
async function ADDFRIEND(host,friend){
    const currentTime = new Date();
    const formattedTime = currentTime.toISOString().slice(0, 19).replace('T', ' ');
    const promisePool = pool.promise();

    try{
        const [rows,fileds] = await promisePool.query('INSERT `Friends`(`host`,`friend`,`set_date`)VALUES(?,?,?)',[host,friend,formattedTime]);
        const [rows_o,fileds_o] = await promisePool.query('INSERT `Friends`(`host`,`friend`,`set_date`)VALUES(?,?,?)',[friend,host,formattedTime]);
        const [rows_d,fildes_d] = await promisePool.query('DELETE FROM `friends_applicant`WHERE `aim`=?',[host]);
        return {tips:'添加好友成功',msg1:friend,msg2:host,rows,rows_o,rows_d};
    }catch(error){
        return {tips:'添加好友失败',msg:error.message}
    }
}



async function GETUSERBYUSERNAMEOREMAIL(identifier) {  
    const promisePool = pool.promise();  
  
    // 尝试通过用户名查找  
    try {  
        const [rows, fields] = await promisePool.query('SELECT * FROM `User` WHERE `user_name`=?', [identifier]);  
        if (rows.length > 0) {  
            return rows[0]; // 如果有多个相同用户名的用户，这里只会返回第一个  
        }  
    } catch (error) {  
        console.error('Error fetching user by username:', error);  
        // 忽略错误，继续尝试通过邮箱查找  
    }  
  
    // 尝试通过邮箱查找  
    try {  
        const [rows, fields] = await promisePool.query('SELECT * FROM `User` WHERE `e_mail`=?', [identifier]);  
        if (rows.length > 0) {  
            console.log(rows[0].tou_img)
            return rows[0]; // 如果有多个相同邮箱的用户，这里只会返回第一个  
        }  
    } catch (error) {  
        console.error('Error fetching user by email:', error);  
        // 如果没有找到用户或发生错误，返回null  
        return null;  
    }  
    // 如果没有找到用户，返回null  
    return null;  
}  


async function GETUSERBYID(userId) {    
    const promisePool = pool.promise();
    // console.log(userId) 
    try {    
        const [rows, fields] = await promisePool.query('SELECT * FROM `User` WHERE `id`=?', [userId]);  // 假设User表有一个id字段  
        // console.log(rows);
        if (rows.length > 0) {    
            return rows[0];    
        }
        return null   
    } catch (error) {    
        console.error('Error fetching user by ID:', error);    
        return 3;    
    } 
    //return null    
}



// 更新密码
async function UPDATEPASSWORD(userId, newHashedPassword) {  
    const promisePool = pool.promise();  
    try {  
        // console.log(userId, newHashedPassword)
        // 使用UPDATE语句更新User表中的password字段  
        const [rowsAffected] = await promisePool.query(  
            'UPDATE `User` SET `password` = ? WHERE `id` = ?',  
            [newHashedPassword, userId]  
        );  
  
        // 如果 rowsAffected 大于 0，表示至少更新了一行数据  
        if (rowsAffected.affectedRows > 0) {  
            // console.log(rowsAffected)
            return { success: true, msg: '密码更新成功' };  
        } else {  
            // 如果没有行被更新，可能是因为用户ID不存在  
            // console.log(rowsAffected)
            return { success: false, msg: '用户不存在或密码更新失败' };  
        }  
    } catch (error) {  
        console.error('更新密码时出错:', error);  
        return { success: false, msg: '更新密码时出错', error: error.message };  
    }  
}  


// 更新用户名
async function UPDATEUSERNAME(userId, newUsername) {  
    const promisePool = pool.promise();  
    try {  
        // console.log(userId, newUsername)
        // 使用UPDATE语句更新User表中的password字段  
        const [rowsAffected] = await promisePool.query(  
            'UPDATE `User` SET `user_name` = ? WHERE `id` = ?',  
            [newUsername, userId]  
        );  
  
        // 如果 rowsAffected 大于 0，表示至少更新了一行数据  
        if (rowsAffected.affectedRows > 0) {  
            // console.log(rowsAffected)
            return { success: true, msg: '用户名更新成功' };
        } else {  
            // 如果没有行被更新，可能是因为用户ID不存在  
            // console.log(rowsAffected)
            return { success: false, msg: '用户不存在或用户名更新失败' };  
        }  
    } catch (error) {  
        console.error('更新用户名时出错:', error);  
        return { success: false, msg: '更新用户名时出错', error: error.message };  
    }  
}  

// 更新邮箱
async function UPDATEEMAIL(userId, newEmail) {  
    const promisePool = pool.promise();  
    try {  
        console.log(userId, newEmail)
        // 使用UPDATE语句更新User表中的password字段  
        const [rowsAffected] = await promisePool.query(  
            'UPDATE `User` SET `e_mail` = ? WHERE `id` = ?',  
            [newEmail, userId]  
        );  
  
        // 如果 rowsAffected 大于 0，表示至少更新了一行数据  
        if (rowsAffected.affectedRows > 0) {  
            // console.log(rowsAffected)
            return { success: true, msg: '邮箱更新成功' };
        } else {  
            // 如果没有行被更新，可能是因为用户ID不存在  
            // console.log(rowsAffected)
            return { success: false, msg: '用户不存在或邮箱更新失败' };  
        }  
    } catch (error) {  
        console.error('更新邮箱时出错:', error);  
        return { success: false, msg: '更新邮箱时出错', error: error.message };  
    }  
}  

// 更新性别
async function UPDATESEX(userId, newSex) {  
    const promisePool = pool.promise();  
    try {  
        console.log(userId, newSex)
        // 使用UPDATE语句更新User表中的password字段  
        const [rowsAffected] = await promisePool.query(  
            'UPDATE `User` SET `sex` = ? WHERE `id` = ?',  
            [newSex, userId]  
        );  
  
        // 如果 rowsAffected 大于 0，表示至少更新了一行数据  
        if (rowsAffected.affectedRows > 0) {  
            // console.log(rowsAffected)
            return { success: true, msg: '性别更新成功' };
        } else {  
            // 如果没有行被更新，可能是因为用户ID不存在  
            // console.log(rowsAffected)
            return { success: false, msg: '用户不存在或性别更新失败' };  
        }  
    } catch (error) {  
        console.error('更新性别时出错:', error);  
        return { success: false, msg: '更新性别时出错', error: error.message };  
    }  
}  

// 更新签名
async function UPDATESIGN(userId, newSign) {  
    const promisePool = pool.promise();  
    try {  
        console.log(userId, newSign)
        // 使用UPDATE语句更新User表中的password字段  
        const [rowsAffected] = await promisePool.query(  
            'UPDATE `User` SET `sign` = ? WHERE `id` = ?',  
            [newSign, userId]  
        );  
  
        // 如果 rowsAffected 大于 0，表示至少更新了一行数据  
        if (rowsAffected.affectedRows > 0) {  
            // console.log(rowsAffected)
            return { success: true, msg: '签名更新成功' };
        } else {  
            // 如果没有行被更新，可能是因为用户ID不存在  
            // console.log(rowsAffected)
            return { success: false, msg: '用户不存在或签名更新失败' };  
        }  
    } catch (error) {  
        console.error('更新签名时出错:', error);  
        return { success: false, msg: '更新签名时出错', error: error.message };  
    }  
}  


// 查询用户所有信息
async function GETUSERBYIDWITHFULLINFO(userId) {  
    const promisePool = pool.promise();  
    try {  
        // 假设我们想要获取User表中的所有字段  
        const [rows, fields] = await promisePool.query('SELECT * FROM `User` WHERE `id`=?', [userId]);  
        if (rows.length > 0) {  
            return rows[0]; // 返回第一个匹配的用户信息（假设ID是唯一的）  
        }  
        return null; // 如果没有找到用户，返回null  
    } catch (error) {  
        // console.log(rows)
        console.error('获取用户完整信息时出错:', error);  
        throw error; // 或者返回一个错误对象，取决于你的错误处理策略  
    }  
}  

// 查询用户收藏信息
async function SEARCHUSERCOLLECT(username) {  
    const promisePool = pool.promise();  
    try {  
        // 假设我们想要获取User表中的所有字段  
        const [rows, fields] = await promisePool.query('SELECT * FROM `Food_collect` WHERE `user_name`=?', [username]);  
        if (rows.length > 0) {  
            console.log(rows)
            return rows; // 返回第一个匹配的用户信息  
        }  
        return null; // 如果没有找到用户，返回null  
    } catch (error) {  
        // console.log(rows)
        console.error('获取用户完整信息时出错:', error);  
        throw error; // 或者返回一个错误对象，取决于你的错误处理策略  
    }  
}  

// 删除用户收藏信息
async function DELETEUSERCOLLECT(collectId) {
    const promisePool = pool.promise();
    try {
        const [rows, fields] = await promisePool.query('DELETE FROM `Food_collect` WHERE `collect_id`=?', [collectId]);
        if (rows.affectedRows > 0) {
            return { success: true, msg: '收藏删除成功' };
        } else {
            console.log(rows.affectedRows)
            return { success: false, msg: '收藏删除失败，可能是因为该ID不存在' };
        }
    } catch (error) {
        console.error('删除收藏时出错:', error);
        return { success: false, msg: '删除收藏时出错', error: error.message };
    }
}


// 模糊搜索菜名
async function SEARCHDISHS(keyword) {
    const promisePool = pool.promise();
    try {
        // 查询 Publish_temporary_storage 表
        const [rows, fields] = await promisePool.query('SELECT `foodname` FROM `Publish_temporary_storage` WHERE `foodname` REGEXP ? AND `bool_num`=1', [keyword]);
        
        if (rows.length > 0) {
            return { success: true, data: rows };
        } else {
            // 如果在 Publish_temporary_storage 表中未查询到结果，则查询 Tag 表
            const [tagRows, tagFields] = await promisePool.query('SELECT `id` FROM `Tag` WHERE `tag_name` REGEXP ?', [keyword]);
            if (tagRows.length > 0) {
                // 从 Tag 表中获取所有匹配的 ID
                const tagIds = tagRows.map(row => row.id);

                // 在 Publish_temporary_storage 表中根据这些 ID 查询 foodname
                const [foodRows, foodFields] = await promisePool.query(
                    'SELECT `foodname` FROM `Publish_temporary_storage` WHERE `id` IN (?) AND `bool_num`=1',
                    [tagIds]
                );

                return { success: true, data: foodRows };
            } else {
                return { success: false, msg: '未找到匹配的结果' };
            }
        }
    } catch (error) {
        console.error('搜索时出错:', error);
        return { success: false, msg: '搜索时出错', error: error.message };
    }
}


// 查询食谱详细信息
async function SEARCHDISHSDETAIL(foodname) {
    const promisePool = pool.promise();
    try {
        // 查询 Publish_temporary_storage 表
        const [rows, fields] = await promisePool.query('SELECT * FROM `Publish_temporary_storage` WHERE `foodname`=?', [foodname]);

        if (rows.length > 0) {
            const recipeId = rows[0].id;

            // 查询 Main 表
            const [mainRows, mainFields] = await promisePool.query('SELECT * FROM `Main` WHERE `id`=?', [recipeId]);

            // 查询 Others 表
            const [otherRows, otherFields] = await promisePool.query('SELECT * FROM `Others` WHERE `id`=?', [recipeId]);


            // 将 Main 表和 Others 表中的数据放进 rows[0] 中
            rows[0].mainMaterials = mainRows;
            rows[0].otherMaterials = otherRows;

            console.log(rows[0].tips)

            return { success: true, msg: '查询成功', data: rows[0] };
        } else {
            return { success: false, msg: '查询失败，可能是因为该食谱ID不存在' };
        }
    } catch (error) {
        console.error('查询出错:', error);
        return { success: false, msg: '查询出错', error: error.message };
    }
}


async function SEARCHDISHSSTEP(foodname) {  
    const promisePool = pool.promise();
    console.log(foodname)
    try {
        // 查询 Publish_temporary_storage 表
        const [rows, fields] = await promisePool.query('SELECT `id` FROM `Publish_temporary_storage` WHERE `foodname`=?', [foodname]);
        console.log(rows[0].id)

        if (rows.length > 0) {
            const recipeId = rows[0].id;
            const [step, stepFields] = await promisePool.query('SELECT `step` FROM `Step` WHERE `id`=?', [recipeId]);
            const [stepImg, stepImgFields] = await promisePool.query('SELECT `step_img` FROM `Step_img` WHERE `id`=?', [recipeId]);
            rows[0].step = step;
            rows[0].stepImg = stepImg;
            console.log(rows[0].step)
            return { success: true, msg: '查询成功', data: rows[0] };
        } else {
            return { success: false, msg: '查询失败，可能是因为该食谱ID不存在' };
        }
    } catch (error) {
        console.error('查询出错:', error);
        return { success: false, msg: '查询出错', error: error.message };
    }
} 













module.exports = {  
    REGISTER,  
    DEBARK,  
    ADDLEVEL,  
    RENAME,  
    ADDFRIEND,  
    APPLICANT,  
    RECEIVE,   
    GETUSERBYUSERNAMEOREMAIL,
    GETUSERBYID,
    UPDATEPASSWORD,
    UPDATEUSERNAME,
    UPDATEEMAIL,
    GETUSERBYIDWITHFULLINFO,

    UPDATESEX,
    UPDATESIGN,
    SEARCHUSERCOLLECT,
    DELETEUSERCOLLECT,
    SEARCHDISHS,
    SEARCHDISHSDETAIL,
    SEARCHDISHSSTEP
    

};